﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using TRPOP.CRM.Component.Model;
using TRPOP.Common;

namespace TRPOP.CRM.Component.DALSQLServer
{
    public class DepartmentDAL
    {

        private static TRPOP.ComSqlDAL.ICommonDAL CommonDAL = new TRPOP.ComSqlDAL.CommonDAL();
        #region 私有的方法
        ///	<summary>
        ///	从Table取数据到实体
        ///	</summary>
        ///	<param name="oDataRow">数据行</param>
        ///	<returns>返回实体</returns>
        private static DepartmentInfo ParseRowTomodel(DataRow oDataRow)
        {
            DepartmentInfo oInfo = new DepartmentInfo();
            oInfo.iParentDepartmentID = oDataRow["iParentDepartmentID"] is DBNull ? 0 : (int)oDataRow["iParentDepartmentID"];
            oInfo.nvcDepartmentCode = oDataRow["nvcDepartmentCode"] is DBNull ? "" : (string)oDataRow["nvcDepartmentCode"];
            oInfo.nvcDepartmentName = oDataRow["nvcDepartmentName"] is DBNull ? "" : (string)oDataRow["nvcDepartmentName"];
            oInfo.nvcDiscription = oDataRow["nvcDiscription"] is DBNull ? "" : (string)oDataRow["nvcDiscription"];
            oInfo.nvcRemark = oDataRow["nvcRemark"] is DBNull ? "" : (string)oDataRow["nvcRemark"];
            oInfo.dtCreationTime = oDataRow["dtCreationTime"] is DBNull ? (new DateTime(1900, 1, 1)) : (DateTime)oDataRow["dtCreationTime"];
            oInfo.bIsDelete = oDataRow["bIsDelete"] is DBNull ? false : (bool)oDataRow["bIsDelete"];
            oInfo.ntExtendedProperity = oDataRow["ntExtendedProperity"] is DBNull ? "" : (string)oDataRow["ntExtendedProperity"];
            oInfo.iDepartmentID = oDataRow["iDepartmentID"] is DBNull ? 0 : (int)oDataRow["iDepartmentID"];
            return oInfo;
        }


        ///	<summary>
        ///	通用的添加存储过程的变量
        ///	</summary>
        ///	<param name="osdDataBase">SqlDatabase</param>
        ///	<param name="odbCommand">DbCommand</param>
        ///	<param name="oInfo">实体</param>
        private static DepartmentInfo ParseModeltoParam(SqlDatabase odbDataBase, DbCommand odbCommand, DepartmentInfo oInfo)
        {
            odbDataBase.AddInParameter(odbCommand, "@iParentDepartmentID", System.Data.DbType.Int32, oInfo.iParentDepartmentID);
            odbDataBase.AddInParameter(odbCommand, "@nvcDepartmentCode", System.Data.DbType.String, oInfo.nvcDepartmentCode);
            odbDataBase.AddInParameter(odbCommand, "@nvcDepartmentName", System.Data.DbType.String, oInfo.nvcDepartmentName);
            odbDataBase.AddInParameter(odbCommand, "@nvcDiscription", System.Data.DbType.String, oInfo.nvcDiscription);
            odbDataBase.AddInParameter(odbCommand, "@nvcRemark", System.Data.DbType.String, oInfo.nvcRemark);
            odbDataBase.AddInParameter(odbCommand, "@dtCreationTime", System.Data.DbType.DateTime, oInfo.dtCreationTime);
            odbDataBase.AddInParameter(odbCommand, "@bIsDelete", System.Data.DbType.Boolean, oInfo.bIsDelete);
            odbDataBase.AddInParameter(odbCommand, "@ntExtendedProperity", System.Data.DbType.String, oInfo.ntExtendedProperity);
            odbDataBase.AddParameter(odbCommand, "@iDepartmentID", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, oInfo.iDepartmentID);
            return oInfo;
        }


        #endregion

        ///	<summary>
        ///	 更新或添加
        ///	</summary>
        ///	<param name="oInfo"></param>
        ///	<returns>bool</returns>
        public bool Save(DepartmentInfo oInfo)
        {
            bool flag = false;
            try
            {
                SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DbCommand odbCommand = odbDataBase.GetStoredProcCommand("Department_Save");
                ParseModeltoParam(odbDataBase, odbCommand, oInfo);
                odbDataBase.ExecuteNonQuery(odbCommand);
                oInfo.iDepartmentID = (int)odbDataBase.GetParameterValue(odbCommand, "@iDepartmentID");
                flag = true;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return flag;
        }

        ///	<summary>
        ///	根据Primary Key获取信息实体
        ///	</summary>
        ///	<param name="intiDepartmentID"></param>
        ///	<returns>DepartmentInfo</returns>
        public DepartmentInfo GetInfoById(int intiDepartmentID)
        {
            DepartmentInfo oInfo = null;
            string strSqlString = "SELECT * FROM Department WHERE  iDepartmentID=" + intiDepartmentID + "    ";
            try
            {
                DataSet oDataSet = CommonDAL.GetDataSet(strSqlString, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                if (oDataTable.Rows.Count == 1)
                {
                    oInfo = ParseRowTomodel(oDataTable.Rows[0]);
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }

        ///	<summary>
        ///	 根据Primary Key删除信息实体
        ///	</summary>
        ///	<param name="intiDepartmentID"></param>
        ///	<returns>bool</returns>
        public bool DeleteInfoById(int intiDepartmentID)
        {
            string strSqlString = "DELETE FROM Department WHERE   iDepartmentID=" + intiDepartmentID + "    ";
            try
            {
                return CommonDAL.ExeSQLQuery(strSqlString, SystemInfoHelper.getSystemInfo().DataBaseConnection) == EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
        }

        ///	<summary>
        ///	 获取信息列表（无分页）
        ///	</summary>
        ///	<param name="intTopn">前N条，为0时表示所有符合条件的</param>
        ///	<param name="strCondition">自定义条件，要以And开头</param>
        ///	<param name="strOrderby">排序，不需要Order by </param>
        ///	<returns>List<DepartmentInfo></returns>
        public List<DepartmentInfo> GetListByCondition(int intTopn, string strCondition, string strOrderby)
        {
            List<DepartmentInfo> oList = new List<DepartmentInfo>();
            string strSql = CommonDAL.strbuildSqlForList(intTopn, strCondition, strOrderby, "Department");
            try
            {
                DataSet oDataSet = CommonDAL.GetDataSet(strSql, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                foreach (DataRow otempRow in oDataTable.Rows)
                {
                    oList.Add(ParseRowTomodel(otempRow));
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oList;
        }

        ///	<summary>
        ///	获取分页信息
        ///	</summary>
        /// <param name="intPageNumber">当前页页码</param>
        /// <param name="intPageSize">分页大小</param>
        /// <param name="strCondition">查询条件 and ....</param>
        /// <param name="strOrderby">排序的字段名 id desc</param>
        /// <param name="intRecordCount">总记录数</param>
        ///	<returns>List<DepartmentInfo></returns>
        public List<DepartmentInfo> GetListByPager(int intPageNumber, int intPageSize, string strCondition, string strOrderby, out int intRecordCount)
        {
            List<DepartmentInfo> oList = new List<DepartmentInfo>();
            try
            {
                DataSet oDataSet = CommonDAL.GetListByPager("Department", "*", intPageNumber, intPageSize, strCondition, strOrderby, out  intRecordCount, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                foreach (DataRow otempRow in oDataTable.Rows)
                {
                    oList.Add(ParseRowTomodel(otempRow));
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oList;
        }

        /// <summary>
        /// 获得带等级的List
        /// </summary>
        /// <param name="intParentId"></param>
        /// <param name="intLevel"></param>
        /// <param name="strCondition"></param>
        /// <returns></returns>
        public static List<DepartmentLevel> GetListTree(int intParentId, int intLevel, string strCondition)
        {
            if (strCondition.IsEmpty())
            {
                strCondition = "AND bIsDelete=0";
            }

            List<DepartmentLevel> oList = new List<DepartmentLevel>();

            if (intParentId > 0)
            {
                DepartmentLevel oDepartmentInfoLevel = GetInfoLevelById(intParentId);
                if (oDepartmentInfoLevel != null)
                {
                    oDepartmentInfoLevel.iLevel = intLevel + 1;
                    oList.Add(oDepartmentInfoLevel);
                    intLevel = intLevel + 1;
                }
            }

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().DataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand("Department_GetListTree");
            odbDataBase.AddInParameter(odbCommand, "@ParentId", System.Data.DbType.Int32, intParentId);
            odbDataBase.AddInParameter(odbCommand, "@Level", System.Data.DbType.Int32, intLevel);
            odbDataBase.AddInParameter(odbCommand, "@Condition", System.Data.DbType.String, strCondition);
            try
            {
                DataTableCollection dataTables = odbDataBase.ExecuteDataSet(odbCommand).Tables;
                foreach (DataTable table in dataTables)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        oList.Add(ParseRowTomodelLevel(row));
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oList;
        }

        ///	<summary>
        ///	根据Primary Key获取信息实体
        ///	</summary>
        ///	<param name="intiDepartmentID"></param>
        ///	<returns>DepartmentInfo</returns>
        public static DepartmentLevel GetInfoLevelById(int intiDepartmentID)
        {
            DepartmentLevel oInfo = null;
            string strSqlString = "SELECT * FROM Department WHERE  iDepartmentID=" + intiDepartmentID + "    ";
            try
            {
                DataSet oDataSet = CommonDAL.GetDataSet(strSqlString, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                if (oDataTable.Rows.Count == 1)
                {
                    oInfo = ParseRowTomodelLevel(oDataTable.Rows[0]);
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }

        /// <summary>
        /// 带等级的部门信息 转换方法
        /// </summary>
        /// <param name="oDataRow"></param>
        /// <returns></returns>
        private static DepartmentLevel ParseRowTomodelLevel(DataRow oDataRow)
        {
            DepartmentLevel oInfo = new DepartmentLevel();
            oInfo.iDepartmentID = oDataRow["iDepartmentID"] is DBNull ? 0 : (int)oDataRow["iDepartmentID"];
            oInfo.iParentDepartmentID = oDataRow["iParentDepartmentID"] is DBNull ? 0 : (int)oDataRow["iParentDepartmentID"];
            oInfo.nvcDepartmentCode = oDataRow["nvcDepartmentCode"] is DBNull ? "" : (string)oDataRow["nvcDepartmentCode"];
            oInfo.nvcDepartmentName = oDataRow["nvcDepartmentName"] is DBNull ? "" : (string)oDataRow["nvcDepartmentName"];
            oInfo.nvcDiscription = oDataRow["nvcDiscription"] is DBNull ? "" : (string)oDataRow["nvcDiscription"];
            oInfo.nvcRemark = oDataRow["nvcRemark"] is DBNull ? "" : (string)oDataRow["nvcRemark"];
            oInfo.dtCreationTime = oDataRow["dtCreationTime"] is DBNull ? (new DateTime(1900, 1, 1)) : (DateTime)oDataRow["dtCreationTime"];
            oInfo.bIsDelete = oDataRow["bIsDelete"] is DBNull ? false : (bool)oDataRow["bIsDelete"];
            oInfo.ntExtendedProperity = oDataRow["ntExtendedProperity"] is DBNull ? "" : (string)oDataRow["ntExtendedProperity"];
            if (oDataRow.Table.Columns.Contains("Level"))
            {
                oInfo.iLevel = oDataRow["Level"] is DBNull ? 0 : (int)oDataRow["Level"];
            }
            return oInfo;
        }
    }
}
